package com.example.myapplication.data.database

import androidx.room.*
import kotlinx.coroutines.flow.Flow
import com.example.myapplication.data.model.StorageSpaceWithCount

/**
 * 储物空间数据访问对象
 */
@Dao
interface StorageSpaceDao {
    
    /**
     * 获取所有顶级储物空间（parentId为null）
     */
    @Query("SELECT * FROM storage_spaces WHERE parent_id IS NULL ORDER BY name ASC")
    fun getAllTopLevelSpaces(): Flow<List<StorageSpaceEntity>>

    @Transaction
    @Query("""
        SELECT s.*, (SELECT COUNT(*) FROM storage_items WHERE space_id = s.id) as itemCount 
        FROM storage_spaces s 
        WHERE s.parent_id IS NULL 
        ORDER BY s.name ASC
    """)
    fun getTopLevelSpacesWithItemCount(): Flow<List<StorageSpaceWithCount>>
    
    /**
     * 获取所有储物空间
     */
    @Query("SELECT * FROM storage_spaces ORDER BY name ASC") // New function
    fun getAllSpaces(): Flow<List<StorageSpaceEntity>>
    
    /**
     * 根据父ID获取子储物空间
     */
    @Query("SELECT * FROM storage_spaces WHERE parent_id = :parentId ORDER BY name ASC")
    fun getChildSpaces(parentId: String): Flow<List<StorageSpaceEntity>>
    
    /**
     * 根据ID获取储物空间
     */
    @Query("SELECT * FROM storage_spaces WHERE id = :id")
    suspend fun getSpaceById(id: String): StorageSpaceEntity?
    
    /**
     * 根据ID获取储物空间（Flow版本）
     */
    @Query("SELECT * FROM storage_spaces WHERE id = :id")
    fun getSpaceByIdFlow(id: String): Flow<StorageSpaceEntity?>
    
    /**
     * 获取储物空间的完整路径
     */
    @Query("""
        WITH RECURSIVE space_path(id, name, parent_id, level) AS (
            SELECT id, name, parent_id, 0 as level
            FROM storage_spaces 
            WHERE id = :spaceId
            
            UNION ALL
            
            SELECT s.id, s.name, s.parent_id, sp.level + 1
            FROM storage_spaces s
            INNER JOIN space_path sp ON s.id = sp.parent_id
        )
        SELECT * FROM storage_spaces 
        WHERE id IN (SELECT id FROM space_path)
        ORDER BY (SELECT level FROM space_path WHERE space_path.id = storage_spaces.id) DESC
    """)
    suspend fun getSpacePath(spaceId: String): List<StorageSpaceEntity>
    
    /**
     * 搜索储物空间
     */
    @Query("""
        SELECT * FROM storage_spaces 
        WHERE name LIKE '%' || :query || '%' 
        OR description LIKE '%' || :query || '%'
        ORDER BY name ASC
    """)
    fun searchSpaces(query: String): Flow<List<StorageSpaceEntity>>
    
    /**
     * 插入储物空间
     */
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertSpace(space: StorageSpaceEntity): Long
    
    /**
     * 更新储物空间
     */
    @Update
    suspend fun updateSpace(space: StorageSpaceEntity)
    
    /**
     * 删除储物空间
     */
    @Delete
    suspend fun deleteSpace(space: StorageSpaceEntity)
    
    /**
     * 根据ID删除储物空间
     */
    @Query("DELETE FROM storage_spaces WHERE id = :id")
    suspend fun deleteSpaceById(id: String)
    
    /**
     * 检查储物空间是否有子空间
     */
    @Query("SELECT COUNT(*) FROM storage_spaces WHERE parent_id = :spaceId")
    suspend fun hasChildSpaces(spaceId: String): Int
    
    /**
     * 获取储物空间的物品数量
     */
    @Query("SELECT COUNT(*) FROM storage_items WHERE space_id = :spaceId")
    suspend fun getItemCount(spaceId: String): Int
}